package com.hsnn.medstgmini.trading.pur.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.trading.pur.dao.OrderAlreadyDao;
import com.hsnn.medstgmini.trading.pur.model.form.OrderPackage;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;
import com.hsnn.medstgmini.util.PinYin;

/**
 * @category 已配送订单
 * @author 蔡春龙
 * @date 2015年7月15日
 */
@Repository
public class OrderAlreadyDaoImpl implements OrderAlreadyDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	/**
	 * @category 分页查询已配送订单
	 * @date 2015年7月15日
	 * @param orderPackage
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getOrderAlready(OrderPackage orderPackage, Pagination page) {
		Session session = factory.getCurrentSession();

		StringBuffer countSb = new StringBuffer();
		StringBuffer listSb = new StringBuffer();

		listSb.append("SELECT a.*, b.hosp_name hospName,s.name username, c.area_id areaId, ")
		.append(" c.area_name areaName, c.ar_fullname areaFullName, ")
		.append(" DATE_FORMAT(p.del_datetime, '%Y-%m-%d %H:%i:%S') delDatetime, ")
		.append(" DATE_FORMAT(p.recp_datetime, '%Y-%m-%d %H:%i:%S') recpDatetime ")
		.append(" FROM ")
		.append(" ( SELECT SUM(pro_count * pro_price) orderMoney, COUNT(*) orderCount, ")
		.append(" DATE_FORMAT(MAX(submit_datetime), '%Y-%m-%d %T') submitDateTime, ")
		.append(" DATE_FORMAT(MAX(order_confirm_time), '%Y-%m-%d %H:%i:%S') orderConfirmTime, ")
		.append(" order_confirm_user orderConfirmUser, MAX(order_beg_time) beginTime, MAX(order_end_time) endTime, ")
		.append(" order_pack_id orderPackId, hosp_id hospId, SUM( CASE WHEN order_status = 2 THEN 1 ELSE 0 END ) wpsCount, ")
		.append(" SUM( CASE WHEN order_status = 3 THEN 1 ELSE 0 END ) wfpsCount, ")
		.append(" SUM( CASE WHEN order_status = 4 THEN 1 ELSE 0 END ) ypsCount, ")
		.append(" DATE_FORMAT(max(pd.del_datetime),'%Y-%m-%d %T') psDatetime ")
		.append(" FROM pur_orderinfo po left join pur_delorderinfo pd on pd.order_id=po.order_id  ")
		.append(" WHERE 1 = 1 AND (order_status = 2 OR order_status = 3 OR order_status = 4) AND del_comp_id = :delCompId  ")
		.append(" GROUP BY order_pack_id, hosp_id ) a JOIN std_hospinfo b ON a.hospId = b.hosp_id  ")
		.append(" LEFT JOIN dic_area c ON b.area_id = c.area_id  ")
		.append(" left join pur_orderlogistics p on a.orderPackId=p.interflow_id  ")
		.append(" left join sys_user s on a.orderConfirmUser=s.id WHERE 1 = 1 ");

		String orderPackId = orderPackage.getOrderPackId();
		if (StringUtils.isNotBlank(orderPackId)) {
			listSb.append("AND a.orderPackId = :orderPackId ");
		}
		String hospName = orderPackage.getHospName();
		if (StringUtils.isNotBlank(hospName)) {
			if(PinYin.isPinYin(hospName)){
				listSb.append("AND b.hosp_spel_code like :hospName ");
			}else{
				listSb.append("AND b.hosp_name like :hospName ");
			}
		}
		String areaId = orderPackage.getAreaId();
		if (StringUtils.isNotBlank(areaId)) {
			listSb.append("AND c.area_id LIKE :areaId ");
		}

		String startDate = orderPackage.getStartDatestr();
		if (StringUtils.isNotBlank(startDate)) {
			listSb.append("AND DATE_FORMAT(a.submitDateTime, '%Y-%m-%d') >= :startDate ");
		}
		String endDate = orderPackage.getEndDatestr();
		if (StringUtils.isNotBlank(endDate)) {
			listSb.append("AND DATE_FORMAT(a.submitDateTime, '%Y-%m-%d') <= :endDate ");
		}
		
		String confirmStartDate = orderPackage.getConfirmStartDate();
		if (StringUtils.isNotBlank(confirmStartDate)) {
			listSb.append("AND DATE_FORMAT(STR_TO_DATE(a.orderConfirmTime,'%Y-%m-%d %T'),'%Y-%m-%d') >= :confirmStartDate ");
		}
		String confirmEndDate = orderPackage.getConfirmEndDate();
		if (StringUtils.isNotBlank(confirmEndDate)) {
			listSb.append("AND DATE_FORMAT(STR_TO_DATE(a.orderConfirmTime,'%Y-%m-%d %T'),'%Y-%m-%d') <= :confirmEndDate ");
		}
		
		
		
		String psStartDate = orderPackage.getPsStartDate();
		if (StringUtils.isNotBlank(psStartDate)) {
			listSb.append("AND DATE_FORMAT(STR_TO_DATE(a.psDatetime,'%Y-%m-%d %T'),'%Y-%m-%d') >= :psStartDate ");
		}
		String psEndDate = orderPackage.getPsEndDate();
		if (StringUtils.isNotBlank(psEndDate)) {
			listSb.append("AND DATE_FORMAT(STR_TO_DATE(a.psDatetime,'%Y-%m-%d %T'),'%Y-%m-%d') <= :psEndDate ");
		}
		
		String delStartDate = orderPackage.getDelStartDate();
		if (StringUtils.isNotBlank(delStartDate)) {
			listSb.append("AND DATE_FORMAT(p.del_datetime,'%Y-%m-%d') >= :delStartDate ");
		}
		String delEndDate = orderPackage.getDelEndDate();
		if (StringUtils.isNotBlank(delEndDate)) {
			listSb.append("AND DATE_FORMAT(p.del_datetime,'%Y-%m-%d') <= :delEndDate ");
		}
		
		String recpStartDate = orderPackage.getRecpStartDate();
		if (StringUtils.isNotBlank(recpStartDate)) {
			listSb.append("AND DATE_FORMAT(p.recp_datetime,'%Y-%m-%d') >= :recpStartDate ");
		}
		String recpEndDate = orderPackage.getRecpEndDate();
		if (StringUtils.isNotBlank(recpEndDate)) {
			listSb.append("AND DATE_FORMAT(p.recp_datetime,'%Y-%m-%d') <= :recpEndDate ");
		}
		listSb.append(" ORDER BY psDatetime desc 	");
		countSb.append("SELECT COUNT(*) FROM ( ");
		countSb.append(listSb.toString());
		countSb.append(") a ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("delCompId", orderPackage.getDelCompId());
		listQuery.setString("delCompId", orderPackage.getDelCompId());

		if (StringUtils.isNotBlank(orderPackId)) {
			countQuery.setString("orderPackId", orderPackId);
			listQuery.setString("orderPackId", orderPackId);
		}
		if (StringUtils.isNotBlank(hospName)) {
			countQuery.setString("hospName", "%" + hospName + "%");
			listQuery.setString("hospName", "%" + hospName + "%");
		}
		if (StringUtils.isNotBlank(areaId)) {
			countQuery.setString("areaId", AreaUtil.escapeAreaId(areaId));
			listQuery.setString("areaId", AreaUtil.escapeAreaId(areaId));
		}
		if (StringUtils.isNotBlank(startDate)) {
			countQuery.setString("startDate", startDate);
			listQuery.setString("startDate", startDate);
		}
		if (StringUtils.isNotBlank(endDate)) {
			countQuery.setString("endDate", endDate);
			listQuery.setString("endDate", endDate);
		}
		if (StringUtils.isNotBlank(confirmStartDate)) {
			countQuery.setString("confirmStartDate", confirmStartDate);
			listQuery.setString("confirmStartDate", confirmStartDate);
		}
		if (StringUtils.isNotBlank(confirmEndDate)) {
			countQuery.setString("confirmEndDate", confirmEndDate);
			listQuery.setString("confirmEndDate", confirmEndDate);
		}
		if (StringUtils.isNotBlank(delStartDate)) {
			countQuery.setString("delStartDate", delStartDate);
			listQuery.setString("delStartDate", delStartDate);
		}
		if (StringUtils.isNotBlank(psStartDate)){
			countQuery.setString("psStartDate", psStartDate);
			listQuery.setString("psStartDate", psStartDate);
		}
		if (StringUtils.isNotBlank(psEndDate)){
			countQuery.setString("psEndDate", psEndDate);
			listQuery.setString("psEndDate", psEndDate);
		}
		if (StringUtils.isNotBlank(delEndDate)) {
			countQuery.setString("delEndDate", delEndDate);
			listQuery.setString("delEndDate", delEndDate);
		}
		if (StringUtils.isNotBlank(recpStartDate)) {
			countQuery.setString("recpStartDate", recpStartDate);
			listQuery.setString("recpStartDate", recpStartDate);
		}
		if (StringUtils.isNotBlank(recpEndDate)) {
			countQuery.setString("recpEndDate", recpEndDate);
			listQuery.setString("recpEndDate", recpEndDate);
		}
		BigInteger records = (BigInteger) countQuery.uniqueResult();
		page.setRecords(records.longValue());

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null) {
			for (Map<String, Object> map : list) {
				
				if (map.get("orderMoney") != null) {
					BigDecimal orderMoney = (BigDecimal) map.get("orderMoney");
					map.put("orderMoney",
							ParseNumber.toStrDecimal(orderMoney.doubleValue()));
				}
				
//				if (map.get("submitDateTime") != null) {
//					Date submitDateTime = ParseDate.fromFullFormat(map.get("submitDateTime").toString());
//					map.put("submitDateTime", DateUtil.date(submitDateTime));
//				}
//				if (map.get("beginTime") != null) {
//					Date beginTime = ParseDate.fromFullFormat(map.get("beginTime").toString());
//					map.put("beginTime", DateUtil.date(beginTime));
//				}
//				if ( map.get("endTime") != null) {
//					Date endTime = ParseDate.fromFullFormat(map.get("endTime").toString());
//					map.put("endTime", DateUtil.date(endTime));
//				}
			}
		}

		return page;
	}

}
